Use [Card3500]

GO

if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'HTTP8280SyncTable'))
    drop table [HTTP8280SyncTable];
GO
CREATE TABLE [dbo].[HTTP8280SyncTable](
	[SyncID] [bigint] IDENTITY(1,1) NOT NULL,
	[EquptID] [int] NOT NULL,
	[PersonnelID] [int] NOT NULL,
	[SyncType] [int] NOT NULL,
	[InsertTime] [datetime] NOT NULL,
 CONSTRAINT [PK_HTTP8280SyncTable] PRIMARY KEY CLUSTERED 
(
	[SyncID] ASC
));
GO

ALTER TABLE [dbo].[HTTP8280SyncTable] ADD  CONSTRAINT [DF_HTTP8280SyncTable_InsertTime]  DEFAULT (getdate()) FOR [InsertTime];
GO


GO

if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'tr_EmplOfEqupt_Delete_HTTP8280Sync'))
    drop TRIGGER [tr_EmplOfEqupt_Delete_HTTP8280Sync];
GO
-- =============================================
-- Author:		赖金杰
-- Create date: 2021年12月31日
-- Description:	删除人员权限时同步插入到8280同步表中，标识为权限变更(5)
-- =============================================
CREATE TRIGGER [dbo].[tr_EmplOfEqupt_Delete_HTTP8280Sync] on [dbo].[EmplOfEqupt]
   AFTER Delete
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	insert into HTTP8280SyncTable (EquptID,PersonnelID,SyncType)
	select EquptID,0,5 from (select Distinct EquptID from deleted) as a

END
GO
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'tr_EmplOfEqupt_Insert_HTTP8280Sync'))
    drop TRIGGER [tr_EmplOfEqupt_Insert_HTTP8280Sync];
GO
-- =============================================
-- Author:		赖金杰
-- Create date: 2021年12月31日
-- Description:	插入、修改人员权限时同步插入到8280同步表中，标识为权限变更(5)
-- =============================================
CREATE TRIGGER [dbo].[tr_EmplOfEqupt_Insert_HTTP8280Sync] on [dbo].[EmplOfEqupt]
   AFTER insert,update
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	insert into HTTP8280SyncTable (EquptID,PersonnelID,SyncType)
	select EquptID,0,5 from (select Distinct EquptID from inserted) as a

END

GO
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'tr_Personnel_Insert_HTTP8280Sync'))
    drop TRIGGER [tr_Personnel_Insert_HTTP8280Sync];
GO

-- =============================================
-- Author:		赖金杰
-- Create date: 2021年12月31日
-- Description:	插入人员时同步插入到8280同步表中，标识为添加、修改人员
-- =============================================
CREATE TRIGGER [dbo].[tr_Personnel_Insert_HTTP8280Sync] on [dbo].[Personnel]
   AFTER INSERT,update
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	insert into HTTP8280SyncTable (EquptID,PersonnelID,SyncType)
	select 0,PersonnelID,3 from inserted

END
GO

if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'tr_Personnel_Delete_HTTP8280Sync'))
    drop TRIGGER [tr_Personnel_Delete_HTTP8280Sync];
GO
-- =============================================
-- Author:		赖金杰
-- Create date: 2021年12月31日
-- Description:	删除人员时同步插入到8280同步表中，标识为删除人员(4)
-- =============================================
CREATE TRIGGER [dbo].[tr_Personnel_Delete_HTTP8280Sync] on [dbo].[Personnel]
   AFTER Delete
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	insert into HTTP8280SyncTable (EquptID,PersonnelID,SyncType)
	select 0,PersonnelID,4 from deleted

END

GO
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'tr_Door_Update_HTTP8280Sync'))
    drop TRIGGER [tr_Door_Update_HTTP8280Sync];
GO

-- =============================================
-- Author:		赖金杰
-- Create date: 2022年1月11日
-- Description:	更新设备时同步插入到8280同步表中，标识为更新设备
-- =============================================
CREATE TRIGGER [dbo].[tr_Door_Update_HTTP8280Sync] on [dbo].[Equipment]
   AFTER update
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	insert into HTTP8280SyncTable (EquptID,PersonnelID,SyncType)
	select EquptID,0,1 from inserted where COMPort <> 255

END
GO

if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'tr_Door_Delete_HTTP8280Sync'))
    drop TRIGGER [tr_Door_Delete_HTTP8280Sync];
GO
-- =============================================
-- Author:		赖金杰
-- Create date: 2022年1月11日
-- Description:	删除设备时同步到FC8280服务器中，删除缓存
-- =============================================
CREATE TRIGGER [dbo].[tr_Door_Delete_HTTP8280Sync] on [dbo].[Equipment]
   AFTER Delete
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	insert into HTTP8280SyncTable (EquptID,PersonnelID,SyncType)
	select EquptID,0,2 from deleted

END
